﻿using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid;
using Lamex.Base;
using Lamex.Common;
using Lamex.Controls;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Lamex.Library.CommonClass
{
    public class BuildSqlHelper
    {
        public static bool IsExistList(string dtName,string databaseName)
        {
            string dbName = Loginer.CurrentUser.DBName;
            if (Loginer.CurrentUser.DBName == "正式数据库")
            {
                string Msg = "非授权用户，不允许修改数据！";
                if (string.IsNullOrEmpty(Loginer.CurrentUser.Email))
                {
                    XtraMessageBox.Show(Msg);
                    return false;
                }

                string strWhere = "";
                strWhere += " and DATABASE_NAME='"+ databaseName + "' ";
                strWhere += " and TABLE_NAME='" + dtName + "' ";
                if (!string.IsNullOrEmpty(Loginer.CurrentUser.Email))
                {
                    string[] subName = Loginer.CurrentUser.Email.Split('@');
                    //string str = subName[0].Replace('.',' ').Trim().ToUpper();
                    //strWhere += " and UPPER( AUTHORITY_USER_NAME) like '%" + subName[0].ToUpper() + "%'";

                }
                if (dtName == "Sys_TestManage_Record") return true;
                string sql = " select * from Sys_DataAuthority where 1=1 " + strWhere;
                try
                {
                    DataTable dt = BaseBuildSql.GetDataTableBySql(sql);
                    if (dt.Rows.Count > 0)
                    {
                        //if (dt.Rows[0]["TABLE_NAME"].ToString() == "Sys_TestManage_Record")
                        //    return true;
                        string[] subName = Loginer.CurrentUser.Email.Split('@');
                        string str = subName[0].Replace('.', ' ').Trim().ToUpper();
                        if (dt.Rows[0]["AUTHORITY_USER_NAME"].ToString().Replace('.', ' ').Trim().ToUpper().Contains(str))
                        {
                            return true;
                        }
                        else
                        {
                            XtraMessageBox.Show(Msg);
                            return false;
                        }
                    }
                    else
                    {
                        XtraMessageBox.Show(Msg);
                        return false;
                    }

                }
                catch (Exception ex)
                {
                    XtraMessageBox.Show(ex.Message);
                    return false;
                }
            }
            else
            {
                return true;
            }

        }
        public static bool Insert(Panel gc, string tableName,string dataName)
        {
            List<PictrueList> lst = new List<PictrueList>();
            string strFiled = "";
            string filedValue = "";
            string sql = "";
            string strWhere = "";
            try
            {
                foreach (Control c in gc.Controls)
                {
                    if (c is ucTextEdit)
                    {
                        ucTextEdit uc = c as ucTextEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            strWhere += " and " + uc.Name + "='" + uc.Text + "'";
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.Text + "'" + ",";
                    }
                    else if (c is ucComboBoxEdit)
                    {
                        ucComboBoxEdit uc = c as ucComboBoxEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            if (uc.UcFlterEditFlag == "true")
                            {
                                strWhere += " and " + uc.Name + "='" + uc.Text + "'";
                            }
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.Text + "'" + ",";
                    }
                    else if (c is ucLookUpEdit)
                    {
                        ucLookUpEdit uc = c as ucLookUpEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            if (uc.UcFlterEditFlag == "true")
                            {
                                strWhere += " and " + uc.Name + "='" + uc.UnEditValue + "'";
                            }

                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.UnEditValue + "'" + ",";
                    }
                    else if (c is ucDateEdit)
                    {
                        ucDateEdit uc = c as ucDateEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            if (uc.UcFlterEditFlag == "true")
                            {

                                strWhere += " and " + uc.Name + "='" + uc.UcEditValue.ToString() + "'";
                            }
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.UcEditValue.ToString() + "'" + ",";
                    }
                    else if (c is ucDate)
                    {
                        ucDate uc = c as ucDate;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            if (uc.UcFlterEditFlag == "true")
                            {

                                strWhere += " and " + uc.Name + "='" + uc.UcEditValue.ToString() + "'";
                            }
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.UcEditValue.ToString() + "'" + ",";
                    }
                    else if (c is ucMemoEdit)
                    {
                        ucMemoEdit uc = c as ucMemoEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            strWhere += " and " + uc.Name + "='" + uc.Text + "'";
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.Text + "'" + ",";
                    }
                    else if (c is ucCheckEdit)
                    {
                        ucCheckEdit uc = c as ucCheckEdit;
                        if (uc.UcIsFlterEditFlag == true)
                            strWhere += " and " + uc.Name + " = '" + uc.isChecked + "'";
                        strFiled += uc.Name + ",";
                        if (uc.isChecked)
                            filedValue += "1" + ",";
                        else
                            filedValue += "0" + ",";

                    }
                    else if (c is ucPictrueEdit)
                    {
                        ucPictrueEdit uc = c as ucPictrueEdit;
                        strFiled += uc.Name + ",";
                        if (uc.UcImage == null)
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "@" + uc.Name + ",";

                        PictrueList model = new PictrueList();

                        if (uc.UcImage != null)
                        {
                            model.Name = "@" + uc.Name;
                            model.bytes = uc.GetPictureBytes();
                            lst.Add(model);
                        }
                        //else
                        //    model.bytes = null;


                    }
                }

                strFiled = strFiled.Trim(',');
                filedValue = filedValue.Trim(',');
                sql = " INSERT INTO " + dataName+".dbo."+tableName + "( " + strFiled + " ) Values ( " + filedValue + " ) ";
                if (ExecuteNonQuery(sql, lst) > 0)
                {
                    return true;
                }
                else return false;
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
                return false;
            }

        }


        public static bool Insert2(Panel gc, string tableName, string dataName)
        {
            List<PictrueList> lst = new List<PictrueList>();
            string strFiled = "";
            string filedValue = "";
            string sql = "";
            string strWhere = "";
            try
            {
                foreach (Control c in gc.Controls)
                {
                    if (c is ucTextEdit)
                    {
                        ucTextEdit uc = c as ucTextEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            strWhere += " and " + uc.Name + "='" + uc.Text + "'";
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.Text + "'" + ",";
                    }
                    else if (c is ucComboBoxEdit)
                    {
                        ucComboBoxEdit uc = c as ucComboBoxEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            if (uc.UcFlterEditFlag == "true")
                            {
                                strWhere += " and " + uc.Name + "='" + uc.Text + "'";
                            }
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.Text + "'" + ",";
                    }
                    else if (c is ucLookUpEdit)
                    {
                        ucLookUpEdit uc = c as ucLookUpEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            if (uc.UcFlterEditFlag == "true")
                            {
                                strWhere += " and " + uc.Name + "='" + uc.UnEditValue + "'";
                            }

                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.UnEditValue + "'" + ",";
                    }
                    else if (c is ucDateEdit)
                    {
                        ucDateEdit uc = c as ucDateEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            if (uc.UcFlterEditFlag == "true")
                            {
                                if (uc.UcMaxFlag.ToLower() == "true")
                                    strWhere += " and " + uc.Name + "='" + DateTime.MaxValue.ToString("yyyy-MM-dd hh:mm:ss") + "'";
                                //else
                                //    strWhere += " and " + uc.Name + "='" + uc.Text.ToString() + "'";
                            }
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.UcEditValue.ToString() + "'" + ",";
                    }
                    else if (c is ucDate)
                    {
                        ucDate uc = c as ucDate;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            if (uc.UcFlterEditFlag == "true")
                            {
                                if (uc.UcMaxFlag.ToLower() == "true")
                                    strWhere += " and " + uc.Name + "='" + DateTime.MaxValue.ToString("yyyy-MM-dd") + "'";
                                //else
                                //    strWhere += " and " + uc.Name + "='" + uc.Text.ToString() + "'";
                            }
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.UcEditValue.ToString() + "'" + ",";
                    }
                    else if (c is ucMemoEdit)
                    {
                        ucMemoEdit uc = c as ucMemoEdit;
                        if (uc.UcFlterEditFlag == "true")
                        {
                            strWhere += " and " + uc.Name + "='" + uc.Text + "'";
                        }
                        strFiled += uc.Name + ",";
                        if (string.IsNullOrEmpty(uc.Text))
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "'" + uc.Text + "'" + ",";
                    }
                    else if (c is ucCheckEdit)
                    {
                        ucCheckEdit uc = c as ucCheckEdit;
                        if (uc.UcIsFlterEditFlag == true)
                            strWhere += " and " + uc.Name + " = '" + uc.isChecked + "'";
                        strFiled += uc.Name + ",";
                        if (uc.isChecked)
                            filedValue += "1" + ",";
                        else
                            filedValue += "0" + ",";

                    }
                    else if (c is ucPictrueEdit)
                    {
                        ucPictrueEdit uc = c as ucPictrueEdit;
                        strFiled += uc.Name + ",";
                        if (uc.UcImage == null)
                            filedValue += "NULL" + ",";
                        else
                            filedValue += "@" + uc.Name + ",";

                        PictrueList model = new PictrueList();

                        if (uc.UcImage != null)
                        {
                            model.Name = "@" + uc.Name;
                            model.bytes = uc.GetPictureBytes();
                            lst.Add(model);
                        }



                    }
                }

                strFiled = strFiled.Trim(',');
                filedValue = filedValue.Trim(',');
                sql = " INSERT INTO " + dataName+".dbo."+tableName + "( " + strFiled + " ) Values ( " + filedValue + " ) ";
                if (ExecuteNonQuery(sql, lst) > 0) return true;
                else return false;
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
                return false;
            }

        }

        public static bool Update(Panel gc, string tableName, string sqlWhere, GridView view, int iRow, string dataName)
        {
            List<PictrueList> lst = new List<PictrueList>();
            string sqlTemp = "";
            string sqlModify = "";
            string strFiled = "";
            string filedValue = "";
            string sql = "";
            string strWhere = "";
            string keyName = "";
            string IsCheck = "";
            string keyStr = "";
            #region"获取主键"
            DataTable isCheckData = null;
            
            string sqlstr = @"SELECT SYSCOLUMNS.name
              FROM SYSCOLUMNS,SYSOBJECTS,SYSINDEXES,SYSINDEXKEYS 
              WHERE SYSCOLUMNS.id = object_id('" + tableName + @"') 
              AND SYSOBJECTS.xtype = 'PK' 
              AND SYSOBJECTS.parent_obj = SYSCOLUMNS.id 
              AND SYSINDEXES.id = SYSCOLUMNS.id 
              AND SYSOBJECTS.name = SYSINDEXES.name 
              AND SYSINDEXKEYS.id = SYSCOLUMNS.id 
              AND SYSINDEXKEYS.indid = SYSINDEXES.indid   
              AND SYSCOLUMNS.colid = SYSINDEXKEYS.colid";
            if ("LamexMESManager".ToLower() == dataName.ToLower())
                isCheckData = BaseBuildSql.GetDataTableBySql(sqlstr);
            else
            {
                //Lamex.Common.IniFile ini = new Common.IniFile(Application.StartupPath + "\\config\\CSFramework3.ini");
                //string serverName= ini.IniReadValue("SQL Configuration", "SqlServer").ToString();
                string testConnect = @"Data Source={0},1433;Initial Catalog={1};User ID={2};Password ={3};Persist Security Info=True;Connection Timeout=30";
                string strConnect = string.Format(testConnect, Loginer.CurrentUser.DataSetID, dataName, "sa", "Sa@1050%");
                isCheckData = GetDataTableBySql(sqlstr, strConnect);
            }

            DataTable multiRowData = BaseBuildSql.GetDataTableBySql(" select * from " + dataName + ".dbo."+ tableName + " where 1=1" + sqlWhere);
            if (multiRowData.Rows.Count >= 2)
            {
                XtraMessageBox.Show("数据经查存在多行,无法修改！");
                return false;
            }
            foreach (DataRow dr in isCheckData.Rows)
            {
                foreach (GridColumn c in view.Columns)
                {
                    if (c.FieldName == dr["name"].ToString())
                    {
                        keyStr += " and " + c.FieldName + "=" + view.GetRowCellDisplayText(iRow, c.FieldName);
                    }
                }

            }

            //keyStr = keyStr.Trim(',');
            #endregion
            try
            {
                int icheckName = 0;
                int icheckDate = 0;
                foreach (Control c in gc.Controls)
                {
                    if (c is ucTextEdit)
                    {
                        ucTextEdit uc = c as ucTextEdit;
                        if (uc.UcKey != "true")
                        {
                            if (!string.IsNullOrEmpty(uc.Text))
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.Text);
                            else
                                strFiled += uc.Name + "=NULL,";
                            #region"保存修改数据"
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                            #endregion
                        }
                    }
                    else if (c is ucComboBoxEdit)
                    {
                        ucComboBoxEdit uc = c as ucComboBoxEdit;
                        if (uc.UcKey != "true")

                        {
                            if (!string.IsNullOrEmpty(uc.Text))
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.Text);
                            else
                                strFiled += uc.Name + "=NULL,";

                            #region"保存修改数据"
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                            #endregion
                        }
                    }
                    else if (c is ucLookUpEdit)
                    {
                        ucLookUpEdit uc = c as ucLookUpEdit;

                        if (uc.UcKey != "true")

                        {
                            if (!string.IsNullOrEmpty(uc.Text))
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.UnEditValue.ToString());
                            else
                                strFiled += uc.Name + "=NULL,";

                            #region"保存修改数据"
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                            #endregion
                        }
                    }
                    else if (c is ucDateEdit)
                    {
                        ucDateEdit uc = c as ucDateEdit;
                        strFiled += string.Format("{0}='{1}',", uc.Name, uc.UcEditValue.ToString());

                        #region"保存修改数据"
                        int iCheck = 1;
                        sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                        sqlModify += sqlTemp;
                        if (iCheck == 2)
                            icheckDate++;
                        #endregion

                    }
                    else if (c is ucDate)
                    {
                        ucDate uc = c as ucDate;
                        if (uc.UcEditValue != null)
                        {
                            if (uc.UcEditValue.ToString() == "9998/12/31 0:00:00")
                                strFiled += string.Format("{0}='{1}',", uc.Name, DateTime.MaxValue);
                            else
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.UcEditValue.ToString());
                        }
                        else
                            strFiled += string.Format("{0}=null,", uc.Name);

                        #region"保存修改数据"
                        int iCheck = 1;
                        sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                        sqlModify += sqlTemp;
                        if (iCheck == 2)
                            icheckDate++;
                        #endregion

                    }
                    else if (c is ucMemoEdit)
                    {
                        ucMemoEdit uc = c as ucMemoEdit;
                        if (uc.UcKey != "true")
                        {
                            if (!string.IsNullOrEmpty(uc.Text))
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.Text);
                            else
                                strFiled += uc.Name + "=NULL,";

                            #region"保存修改数据"
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                            #endregion
                        }
                    }
                    else if (c is ucCheckEdit)
                    {
                        ucCheckEdit uc = c as ucCheckEdit;
                        if (uc.UcKey != "true")
                        {
                            if (uc.isChecked)
                                strFiled += string.Format("{0}={1},", uc.Name, 1);
                            else
                                strFiled += uc.Name + "=0,";
                            #region"保存修改数据"
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                            #endregion
                        }
                    }
                    else if (c is ucPictrueEdit)
                    {
                        ucPictrueEdit uc = c as ucPictrueEdit;
                        if (uc.UcKey != "true")
                        {
                            if (uc.UcImage != null)
                                strFiled += string.Format("{0}={1},", uc.Name, "@" + uc.Name);
                            else
                                strFiled += uc.Name + "=NULL,";
                            PictrueList model = new PictrueList();

                            if (uc.UcImage != null)
                            {
                                model.Name = "@" + uc.Name;
                                model.bytes = uc.GetPictureBytes();
                                lst.Add(model);
                            }
                            #region"保存修改数据"
                            //int iCheck = 0;
                            //sqlTemp = ReSqlStrModify(dataBaseName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            //sqlModify += sqlTemp;
                            //if (iCheck == 1)
                            //    icheckName++;
                            #endregion
                        }
                    }
                }

                strFiled = strFiled.Trim(',');
                sql = string.Format("UPDATE {0} set {1} WHERE 1=1 {2}", dataName + ".dbo." +tableName, strFiled, sqlWhere);
                if (ExecuteNonQuery(sql, lst) > 0)
                {
                    if (icheckName > 0)
                        BaseBuildSql.ExecuteNonQuery(sqlModify);
                    return true;
                }

                else return false;
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
                return false;
            }

        }

        public static bool Update3(Panel gc, string tableName, string sqlWhere,string dataName )
        {
            string strFiled = "";
            string filedValue = "";
            string sql = "";
            string strWhere = "";
            string keyName = "";

            try
            {
                foreach (Control c in gc.Controls)
                {

                    if (c is ucDateEdit)
                    {
                        ucDateEdit uc = c as ucDateEdit;
                        if (uc.UcMaxFlag.ToString().ToLower() == "true")
                            strFiled += string.Format("{0}={1},", uc.Name, "getdate()");

                    }
                    if (c is ucDate)
                    {
                        ucDate uc = c as ucDate;
                        if (uc.UcMaxFlag.ToString().ToLower() == "true")
                            strFiled += string.Format("{0}={1},", uc.Name, "'" + DateTime.Now.ToString("yyyy-MM-dd") + "'");

                    }
                }

                strFiled = strFiled.Trim(',');
                sql = string.Format("UPDATE {0} set {1} WHERE 1=1 {2}", dataName + ".dbo."+tableName, strFiled, sqlWhere);
                if (BaseBuildSql.ExecuteNonQuery(sql) > 0) return true;
                else return false;
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
                return false;
            }

        }
        public static bool Update2(Panel gc, string tableName, string sqlWhere, GridView view, int iRow, string dataName)
        {
            List<PictrueList> lst = new List<PictrueList>();
            string sqlTemp = "";
            string sqlModify = "";
            string strFiled = "";
            string filedValue = "";
            string sql = "";
            string strWhere = "";
            string keyName = "";
            string keyStr = "";
            DataTable multiRowData = BaseBuildSql.GetDataTableBySql(" select * from " + dataName + ".dbo."+tableName + " where 1=1 " + sqlWhere);
            if (multiRowData.Rows.Count >= 2)
            {
                XtraMessageBox.Show("数据经查存在多行,无法修改！");
                return false;
            }
            #region"获取主键"
            DataTable isCheckData = BaseBuildSql.GetDataTableBySql(@"  SELECT SYSCOLUMNS.name
              FROM SYSCOLUMNS,SYSOBJECTS,SYSINDEXES,SYSINDEXKEYS 
              WHERE SYSCOLUMNS.id = object_id('" + tableName + @"') 
              AND SYSOBJECTS.xtype = 'PK' 
              AND SYSOBJECTS.parent_obj = SYSCOLUMNS.id 
              AND SYSINDEXES.id = SYSCOLUMNS.id 
              AND SYSOBJECTS.name = SYSINDEXES.name 
              AND SYSINDEXKEYS.id = SYSCOLUMNS.id 
              AND SYSINDEXKEYS.indid = SYSINDEXES.indid   
              AND SYSCOLUMNS.colid = SYSINDEXKEYS.colid");
            foreach (DataRow dr in isCheckData.Rows)
            {
                foreach (GridColumn c in view.Columns)
                {
                    if (c.FieldName == dr["name"].ToString())
                    {
                        keyStr += " and " + c.FieldName + "=" + view.GetRowCellDisplayText(iRow, c.FieldName);
                    }
                }

            }
            #endregion

            try
            {
                int icheckName = 0;
                int icheckDate = 0;

                foreach (Control c in gc.Controls)
                {
                    if (c is ucTextEdit)
                    {
                        ucTextEdit uc = c as ucTextEdit;
                        if (uc.UcKey != "true")
                        {
                            if (!string.IsNullOrEmpty(uc.Text))
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.Text);
                            else
                                strFiled += uc.Name + "=NULL,";
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                        }
                    }
                    else if (c is ucComboBoxEdit)
                    {
                        ucComboBoxEdit uc = c as ucComboBoxEdit;
                        if (uc.UcKey != "true")

                        {
                            if (!string.IsNullOrEmpty(uc.Text))
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.Text);
                            else
                                strFiled += uc.Name + "=NULL,";
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                        }
                    }
                    else if (c is ucLookUpEdit)
                    {
                        ucLookUpEdit uc = c as ucLookUpEdit;

                        if (uc.UcKey != "true")

                        {
                            if (!string.IsNullOrEmpty(uc.Text))
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.UnEditValue.ToString());
                            else
                                strFiled += uc.Name + "=NULL,";
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;

                        }
                    }
                    else if (c is ucDateEdit)
                    {
                        ucDateEdit uc = c as ucDateEdit;
                        if (uc.UcMaxFlag.ToLower() == "true")
                            strFiled += string.Format("{0}='{1}',", uc.Name, uc.Text);
                        else
                            strFiled += string.Format("{0}='{1}',", uc.Name, uc.UcEditValue.ToString());
                        int iCheck = 1;
                        sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                        sqlModify += sqlTemp;
                        if (iCheck == 2)
                            icheckDate++;


                    }
                    else if (c is ucDate)
                    {
                        ucDate uc = c as ucDate;
                        if (uc.UcMaxFlag.ToLower() == "true")
                            strFiled += string.Format("{0}='{1}',", uc.Name, uc.Text);
                        else
                            strFiled += string.Format("{0}='{1}',", uc.Name, uc.UcEditValue.ToString());
                        int iCheck = 1;
                        sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                        sqlModify += sqlTemp;
                        if (iCheck == 2)
                            icheckDate++;


                    }
                    else if (c is ucMemoEdit)
                    {
                        ucMemoEdit uc = c as ucMemoEdit;
                        if (uc.UcKey != "true")
                        {
                            if (!string.IsNullOrEmpty(uc.Text))
                                strFiled += string.Format("{0}='{1}',", uc.Name, uc.Text);
                            else
                                strFiled += uc.Name + "=NULL,";
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                        }
                    }
                    else if (c is ucCheckEdit)
                    {
                        ucCheckEdit uc = c as ucCheckEdit;
                        if (uc.UcKey != "true")
                        {
                            if (uc.isChecked)
                                strFiled += string.Format("{0}={1},", uc.Name, 1);
                            else
                                strFiled += uc.Name + "=0,";
                            #region"保存修改数据"
                            int iCheck = 0;
                            sqlTemp = ReSqlStrModify(dataName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            sqlModify += sqlTemp;
                            if (iCheck == 1)
                                icheckName++;
                            #endregion
                        }
                    }
                    else if (c is ucPictrueEdit)
                    {
                        ucPictrueEdit uc = c as ucPictrueEdit;
                        if (uc.UcKey != "true")
                        {
                            if (uc.UcImage != null)
                                strFiled += string.Format("{0}={1},", uc.Name, "@" + uc.Name);
                            else
                                strFiled += uc.Name + "=NULL,";
                            PictrueList model = new PictrueList();

                            if (uc.UcImage != null)
                            {
                                model.Name = "@" + uc.Name;
                                model.bytes = uc.GetPictureBytes();
                                lst.Add(model);
                            }
                            #region"保存修改数据"
                            //int iCheck = 0;
                            //sqlTemp = ReSqlStrModify(dataBaseName, tableName, keyStr, view, iRow, uc.Text, uc.Name, ref iCheck);
                            //sqlModify += sqlTemp;
                            //if (iCheck == 1)
                            //    icheckName++;
                            #endregion
                        }
                    }
                }

                strFiled = strFiled.Trim(',');
                sql = string.Format("UPDATE {0} set {1} WHERE 1=1 {2}", tableName, strFiled, sqlWhere);
                if (BaseBuildSql.ExecuteNonQuery(sql) > 0)
                {
                    if (icheckName > 0)
                        BaseBuildSql.ExecuteNonQuery(sqlModify);
                    return true;
                }

                else return false;
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
                return false;
            }

        }

        public static DataTable GetProcType()
        {
            DataTable dt = new DataTable();
            string[] subName = Loginer.CurrentUser.Email.Split('@');
            string userName = subName[0].Trim();
            string sql = @"select PROD_CATEGORY  FROM [LamexMESManager].[dbo].[IE_Prod_Type_Ctrl] where USER_NAME like '%" + userName + "%'";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }

        public static string ReSqlStrModify(string dataBaseName, string tableName, string keyStr, GridView view, int iRow, string strText, string strName, ref int iCheck)
        {
            string sqlTemp = "";

            foreach (GridColumn col in view.Columns)
            {
                if (col.FieldName == strName)
                {
                    if (strText != view.GetRowCellDisplayText(iRow, col.FieldName))
                    {
                        if(IsNumber(view.GetRowCellDisplayText(iRow, col.FieldName)))
                        {
                            string preInt = Convert.ToDecimal(view.GetRowCellDisplayText(iRow, col.FieldName).ToString()).ToString("0.##");
                            string NewInt = Convert.ToDecimal(strText).ToString("0.##");
                            if(preInt!=NewInt)
                            {
                                sqlTemp = @" insert into sys_Data_Modified_Record([DATABASE_NAME],[TABLE_NAME],[PRIMARY_KEYS],[FIELD],[PREVIOUS],[MODIFIED],[UPDATE_USER],[UPDATE_TIME])
                                                    values('" + dataBaseName + "', '" + tableName + "', '" + keyStr + "', '" + col.FieldName + "', '" + view.GetRowCellDisplayText(iRow, col.FieldName) + "','" + strText + "','" + Lamex.Common.Loginer.CurrentUser.AccountName + "',GETDATE()) ;";
                                if (iCheck == 0)
                                    iCheck++;
                            }
                        }
                        else
                        {
                            sqlTemp = @" insert into sys_Data_Modified_Record([DATABASE_NAME],[TABLE_NAME],[PRIMARY_KEYS],[FIELD],[PREVIOUS],[MODIFIED],[UPDATE_USER],[UPDATE_TIME])
                                                    values('" + dataBaseName + "', '" + tableName + "', '" + keyStr + "', '" + col.FieldName + "', '" + view.GetRowCellDisplayText(iRow, col.FieldName) + "','" + strText + "','" + Lamex.Common.Loginer.CurrentUser.AccountName + "',GETDATE()) ;";
                            if (iCheck == 0)
                                iCheck++;
                        }
                        

                    }
                }
            }
            return sqlTemp;
        }


        private static int ExecuteNonQuery(string strSql, List<PictrueList> lst)
        {
            string sConn = SqlConfiguration.CurrentConnectionString; ;

            using (SqlConnection conn = new SqlConnection(sConn))
            {
                using (SqlCommand cmd = new SqlCommand(strSql, conn))
                {
                    try
                    {
                        conn.Open();
                        foreach (PictrueList p in lst)
                        {
                            SqlParameter sqlParameter = new SqlParameter(p.Name, SqlDbType.Image);
                            sqlParameter.Value = p.bytes;
                            cmd.Parameters.Add(sqlParameter);
                        }
                        int rows = cmd.ExecuteNonQuery();
                        conn.Close();
                        return rows;
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                }
            }
        }

        public static bool IsNumber(string s)
        {
            if (string.IsNullOrWhiteSpace(s)) return false;
            string pattern = "^-?\\d+$|^(-?\\d+)(\\.\\d+)?$";
            Regex regex = new Regex(pattern);
            return regex.IsMatch(s);
        }

        // have different database name can use it
        public static DataTable GetDataTableBySql(string sql, string url)
        {
            try
            {
                string sConn = url;
                SqlConnection con = new SqlConnection(sConn);
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = sql;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    CloseConnection(con);
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            
        }
        private static void  CloseConnection(SqlConnection connection)
        {
            if (connection == null) return;
            if (connection.State != ConnectionState.Closed) connection.Close();
        }
    }
}
